package LawsonTransformJava.pgm;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.io.*;
import java.lang.reflect.InvocationTargetException;

import LawsonTransformJava.dao.DAOEmpCodes;
import LawsonTransformJava.dao.DAOFactoryOracleDB;
import LawsonTransformJava.dao.DAObject;
import LawsonTransformJava.data.TblEmpCodes;

public class PA520 extends DAObject implements 
 DAOEmpCodes, Runnable {

 public PA520(File output) {
 // initialization 
		try {
			this.output = new FileWriter(this.outputFile = output);
			init();
		} catch (IOException e) {
			// Could no write properties file
			System.out.println("IOException: " + e);
		}
}

private void init() throws IOException {
	    // Set the data and xref variables
	    data = DAOFactoryOracleDB.getDAOOracleDB(true);
	    
	    sqlData = "SELECT * FROM " + data.getSchema() + "." + TABLE_NAME;
	    stmtData = data.getDataStatement();	    
	    countStmtData = data.getCountStatement();
	    //Set the Date Format for output
	    df = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss");
}



public TblEmpCodes findEmpcodes() {
		try {
			TblEmpCodes epc = new TblEmpCodes();
			sqlData = "SELECT * FROM " + data.getSchema() + "." + TABLE_NAME + (whereClause==null?"":whereClause);
			rslt = data.getDataStatement().executeQuery(sqlData);
			rsmd = rslt.getMetaData();
			if (rslt.next())
				hasMoreRecs = true;
			else
				hasMoreRecs = false;
			epc = (TblEmpCodes) createObject(rslt, epc);
			return epc;			
		} catch (SQLException e) {
		    // Could not connect to the database
			System.out.println("SQLException: " + e);
		} catch (InvocationTargetException ite) {
			System.out.println("InvocationException: " + ite);
		} catch (IllegalAccessException iae) {
			System.out.println("IllegalAccessException: " + iae);
		}
	  return null;
}

public TblEmpCodes findEmpcodes(String employeeNumber) {
	try {		
		TblEmpCodes epc = new TblEmpCodes();
		sqlData = "SELECT * FROM " + data.getSchema() + "." + TABLE_NAME + " WHERE " + TABLE_NAME + "='" + employeeNumber + "'";
		rslt = data.getDataStatement().executeQuery(sqlData);
		rsmd = rslt.getMetaData();
		rslt.next();
		//@ TODO Add the creation of an Employee object here
		epc = (TblEmpCodes) createObject(rslt, epc);
		return epc;
		//createFields(epc);
	} catch (SQLException e) {
	    // Could not connect to the database
		System.out.println("SQLException: " + e);
	} catch (InvocationTargetException ite) {
		System.out.println("InvocationException: " + ite);
	} catch (IllegalAccessException iae) {
		System.out.println("IllegalAccessException: " + iae);
	}
	return new TblEmpCodes();
}

public TblEmpCodes nextEmpcodes() {
		try {
			TblEmpCodes epc = new TblEmpCodes();
			if (rslt.next()) {
				hasMoreRecs = true;
				epc = (TblEmpCodes) createObject(rslt, epc);
			}
			else
				hasMoreRecs = false;
			return epc;
		} catch (SQLException e) {
		    // Could not connect to the database
			System.out.println("SQLException: " + e);
		} catch (InvocationTargetException ite) {
			System.out.println("InvocationException: " + ite);
		} catch (IllegalAccessException iae) {
			System.out.println("IllegalAccessException: " + iae);
		}
	  return null;
}

/*  public boolean updateEmployee(...) {
 // implement update record here using data
 // from the customerData Transfer Object
 // Return true on success, false on failure or
 // error
}
*/

	public boolean hasMore () {
	  return hasMoreRecs;
	}

public int countEmpCodes() {
	try {
		countRecs = data.getCountStatement().executeQuery("SELECT COUNT(*) FROM " + data.getSchema() + "." + TABLE_NAME + (whereClause==null?"":whereClause));
		countRecs.next();
		return recCount=countRecs.getInt(1);
		} catch (SQLException e) {
		    // Could not connect to the database
			System.out.println("SQLException: " + e);
		}
		return 0;
}

public void setWhereClause(String where) {
	  this.whereClause = where;
}

public void run() {
	  TblEmpCodes epc = null;
	  
	  try {
		//Update current date/time
		Date now = new Date();
		System.out.println(outputFile.getName() + " Start " + df.format(now));
		//Write timestamp to output file
		output.write(outputFile.getName() + " Start " + df.format(now) + "\n");
		
		epc = findEmpcodes();
		output.write(writePA520Layout(epc) + "\n");
		System.out.println(whereClause + " " + countEmpCodes());
		while (hasMore()) {
			epc = nextEmpcodes();
			output.write(writePA520Layout(epc) + "\n");
		}
		//Update current date/time
		now = new Date();
		System.out.println(outputFile.getName() + " Finish " + df.format(now));
		//Write timestamp to output file
		output.write(outputFile.getName() + " Finish " + df.format(now) + "\n");
		output.flush();
		output.close();
	} catch (IOException e) {
		// Could not write properties file
		// Could not create output file
		System.out.println("IOException: " + e);
	}

}

	//Output the PA520 format
	public String writePA520Layout(TblEmpCodes epc) {


		return (
				"" + "," +
				"" + "," +
				"\""+"A"+"\"" + "," +
				"1" + "," +
				epc.getR_type() + "," +
				epc.getEmp_app() + "," +
				epc.getEmployee() + "," +
				epc.getApplicant() + "," +
				epc.getCode() + "," +
				epc.getSubject() + "," +
				epc.getDate_acquired() + "," +
				epc.getRenew_date() + "," +
				epc.getPer_rating() + "," +
				epc.getInstructor() + "," +
				epc.getCo_sponsored() + "," +
				epc.getIn_pro_flag() + "," +
				epc.getLic_number() + "," +
				epc.getCost() + "," +
				epc.getRenewal_code() + "," +
				epc.getVerified_flag() + "," +
				epc.getPriority_code() + "," +
				epc.getSkill_source() + "," +
				epc.getProfic_level() + "," +
				epc.getCurrency_code() + "," +
				epc.getBase_currency() + "," +
				epc.getBase_amount() + "," +
				epc.getDate_stamp() + "," +
				epc.getTime_stamp() + "," +
				epc.getUser_id() + "," +
				epc.getDate_returned() + "," +
				epc.getState() + "," +
				epc.getDescription() 

				);
	}
}